﻿<%@ WebHandler Language="C#" Class="EmployeeAuditTrial" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using ReportBusiness;

/**
 * Description: MES用户操作记录查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.1.11
 **/

public class EmployeeAuditTrial : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT t.*
FROM (
SELECT mm.username oper_user_no,
       e.fullname  oper_user_name,
       mm.txndate oper_date,
       '删除' executeaction,
       mm.objectinstanceid employeeid,
       mm.objectname employeename,
       e1.fullname
FROM modelingaudittrailheader mm
LEFT JOIN employee e ON e.employeeid = mm.userid
LEFT JOIN employee e1 ON e1.employeename = mm.objectname
WHERE mm.objecttypename = 'Employee' AND mm.executeaction = 'Delete'";
        //操作类型
        if (jObject["executeaction"] != null && jObject["executeaction"].ToString() == "添加")
        {
            sql += " AND 1=0 ";
        }

        //操作时间
        if (jObject["oper_date"] != null && jObject["oper_date"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["oper_date"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND mm.txndate >= TO_DATE('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND mm.txndate <= TO_DATE('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //操作者员工号
        if (jObject["oper_user_no"] != null && jObject["oper_user_no"].ToString() != "")
        {
            sql += string.Format(" AND mm.username = '{0}'", jObject["oper_user_no"].ToString());
        }

        //操作者姓名
        if (jObject["oper_user_name"] != null && jObject["oper_user_name"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname = '{0}'", jObject["oper_user_name"].ToString());
        }

        //员工号
        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND mm.objectname = '{0}'", jObject["employeename"].ToString());
        }

        //员工姓名
        if (jObject["fullname"] != null && jObject["fullname"].ToString() != "")
        {
            sql += string.Format(" AND e1.fullname = '{0}'", jObject["fullname"].ToString());
        }

        //员工id
        if (jObject["employeeid"] != null && jObject["employeeid"].ToString() != "")
        {
            sql += string.Format(" AND mm.objectinstanceid = '{0}'", jObject["employeeid"].ToString());
        }

        sql += @"
UNION ALL 
SELECT mm.username oper_user_no,
       e.fullname  oper_user_name,
       mm.txndate oper_date,
       '添加' executeaction,
       mm.objectinstanceid employeeid,
       CASE WHEN INSTR(NVL(mm.objectname,e2.employeename),'Copy of') > 0 THEN e2.employeename 
            ELSE NVL(mm.objectname,e2.employeename) 
       END employeename,
       NVL(e1.fullname,e2.fullname) fullname
FROM modelingaudittrailheader mm
LEFT JOIN employee e ON e.employeeid = mm.userid
LEFT JOIN employee e1 ON e1.employeename = mm.objectname
LEFT JOIN employee e2 ON e2.employeeid = mm.objectinstanceid
WHERE mm.objecttypename = 'Employee' AND mm.executeaction = '添加'";
        //操作类型
        if (jObject["executeaction"] != null && jObject["executeaction"].ToString() == "Delete")
        {
            sql += " AND 1=0 ";
        }

        //操作时间
        if (jObject["oper_date"] != null && jObject["oper_date"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["oper_date"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND mm.txndate >= TO_DATE('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND mm.txndate <= TO_DATE('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //操作者员工号
        if (jObject["oper_user_no"] != null && jObject["oper_user_no"].ToString() != "")
        {
            sql += string.Format(" AND mm.username = '{0}'", jObject["oper_user_no"].ToString());
        }

        //操作人姓名
        if (jObject["oper_user_name"] != null && jObject["oper_user_name"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname = '{0}'", jObject["oper_user_name"].ToString());
        }

        //员工号
        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND ((e2.employeename IS NULL AND mm.objectname = '{0}') OR (e2.employeename = '{0}' AND INSTR(NVL(mm.objectname,e2.employeename),'Copy of') > 0))",
                    jObject["employeename"].ToString());
        }

        //员工姓名
        if (jObject["fullname"] != null && jObject["fullname"].ToString() != "")
        {
            sql += string.Format(" AND NVL(e1.fullname,e2.fullname) = '{0}'", jObject["fullname"].ToString());
        }

        //员工id
        if (jObject["employeeid"] != null && jObject["employeeid"].ToString() != "")
        {
            sql += string.Format(" AND mm.objectinstanceid = '{0}'", jObject["employeeid"].ToString());
        }

        sql += " ) t ORDER BY t.oper_date";
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}